# -*- coding: utf-8 -*-

# Define your item pipelines here
#
# Don't forget to add your pipeline to the ITEM_PIPELINES setting
# See: https://doc.scrapy.org/en/latest/topics/item-pipeline.html
import json
import os
import scrapy
from crawl.settings import IMAGES_STORE as images_store
from scrapy.pipelines.images import ImagesPipeline
import pymysql
import time
import traceback
from crawl.items import Patentitem, Articleitem, Gongsiitem, Authoritem, Author_gongsi, Author_patent, Zlfitem, Paperitem

class MyEncoder(json.JSONEncoder):
    def default(self, obj):
        if isinstance(obj, bytes):
            return str(obj, encoding='utf-8')
        return json.JSONEncoder.default(self, obj)

# 将文字信息存入到.json文件中
class CrawlPipeline(object):
    def __init__(self):
        print('start pipeline')
        self.f = open("zlf.json", "w", encoding = 'utf-8')

    def process_item(self, item, spider):
        content = json.dumps(dict(item), ensure_ascii = False, cls=MyEncoder) + ",\n"
        self.f.write(content)
        return item

    def close_spider(self, spider):
        self.f.close()

# 下载文章中的图片
class ImagesDownloadPipeline(ImagesPipeline):
    def get_media_requests(self, item, info):
        images = item.get('images',[])
        for image in images:
            yield scrapy.Request(image)

# 将信息存入到mysql数据库中
class SaveToMySQL(object):
    def __init__(self):
        # 连接mysql数据库
        #self.connect = pymysql.connect(host='localhost', user='root', password='Wasd8456+', db='crawl_qingdao', port=3306)
        self.connect = pymysql.connect(host='rm-bp151dcc75aycqd80to.mysql.rds.aliyuncs.com', user='root', password='Iscas123', db='knowledgeproject_new', port=3306)
        self.cursor = self.connect.cursor()

    def process_item(self, item, spider):
        cur_time = time.strftime("%Y-%m-%d %H:%M:%S")
        if isinstance(item, Zlfitem) or isinstance(item, Paperitem):
            return item

        if isinstance(item, Author_gongsi) or isinstance(item, Author_patent):
            if item[item.zhujian] is None:
                return item
            for author in item['authors']:
                search1 = 'SELECT id FROM {} ' \
                  'WHERE (name = "{}")'.format('cra_author', author)
                self.cursor.execute(search1)
                results1 = self.cursor.fetchone()

                search2 = 'SELECT id FROM {} ' \
                  'WHERE ({} = "{}")'.format(item.table1, item.set, item[item.zhujian])
                self.cursor.execute(search2)
                results2 = self.cursor.fetchone()

                if results1 is not None and results2 is not None:
                    search3 = 'SELECT id FROM {} WHERE (author_id = {} and {}={})'.format(item.table, results1[0], item.id, results2[0])
                    self.cursor.execute(search3)
                    results = self.cursor.fetchall()
                    if len(results) > 0:
                        return item
                    sqlele = 'INSERT into {}(author_id, {}) VALUES ({}, {})'.format(item.table, item.id, results1[0], results2[0])
                    self.cursor.execute(sqlele)
                    self.connect.commit()
            return item


        sqlele = ""
        # 判断有没有重复的数据
        if isinstance(item, Patentitem) or isinstance(item, Articleitem):
            sqlele = 'SELECT title FROM {} ' \
                  'WHERE (title = "{}")'.format(item.table, item['title'])
        elif isinstance(item, Gongsiitem) or isinstance(item, Authoritem):
            sqlele = 'SELECT name FROM {} ' \
                  'WHERE (name = "{}")'.format(item.table, item['name'])

        try:
            self.cursor.execute(sqlele)
            results = self.cursor.fetchall()
            # 如果有重复的标题则跳过不写
            # TODO(liujunfeng):之后会改变这条数据的信息，并更新update_at信息为current_time
            if len(results) > 0:
                sqlupdate = item.update_command(item, item[item.zhujian], cur_time, cur_time, cur_time)
                self.cursor.execute(sqlupdate)
                self.connect.commit()

                if isinstance(item, Patentitem):
                    search1 = 'SELECT id FROM {} ' \
                              'WHERE (title = "{}")'.format('cra_patent', item['title'])
                    self.cursor.execute(search1)
                    print(search1)
                    results1 = self.cursor.fetchone()
                    print(results1[0])

                    search2 = 'SELECT id FROM {} ' \
                              'WHERE (keyword = "{}")'.format('technique_words', item['keyword'])
                    print(search2)
                    self.cursor.execute(search2)
                    results2 = self.cursor.fetchone()
                    print(results2[0])

                    if results1 is not None and results2 is not None:
                        search3 = 'SELECT id FROM rel_article_keywords WHERE (article_id = {} and type = 2)'.format(
                            results1[0])
                        print(search3)
                        self.cursor.execute(search3)
                        results = self.cursor.fetchall()
                        if len(results) > 0:
                            return item
                        sqlinsert = 'INSERT into rel_article_keywords(article_id, keyword_id, type) VALUES ({}, {}, 2)'.format(
                            results1[0], results2[0])

                        print(sqlinsert)
                        print(results1[0])
                        print(results2[0])
                        self.cursor.execute(sqlinsert)
                        self.connect.commit()
                return item
        except Exception:
            self.connect.rollback()
            print("发生异常",Exception)
            traceback.print_exc()

        sqlele = item.insert_command(item, cur_time, cur_time, cur_time)

        try:
            # 执行sql语句
            self.cursor.execute(sqlele)
            # 提交到数据库执行
            self.connect.commit()
            if isinstance(item, Patentitem):
                # patent的序号
                search1 = 'SELECT id FROM {} ' \
                          'WHERE (title = "{}")'.format('cra_patent', item['title'])
                self.cursor.execute(search1)
                print(search1)
                results1 = self.cursor.fetchone()
                print(results1[0])

                # 关键词的序号
                search2 = 'SELECT id FROM {} ' \
                          'WHERE (keyword = "{}")'.format('technique_words', item['keyword'])
                print(search2)
                self.cursor.execute(search2)
                results2 = self.cursor.fetchone()
                print(results2[0])

                # 如果都存在则插入rel_article_keywords
                if results1 is not None and results2 is not None:
                    search3 = 'SELECT id FROM rel_article_keywords WHERE (article_id = {} and type = 2)'.format(
                        results1[0])
                    print(search3)
                    self.cursor.execute(search3)
                    results = self.cursor.fetchall()
                    if len(results) > 0:
                        return item
                    sqlinsert = 'INSERT into rel_article_keywords(article_id, keyword_id, type) VALUES ({}, {}, 2)'.format(
                        results1[0], results2[0])

                    print(sqlinsert)
                    print(results1[0])
                    print(results2[0])
                    self.cursor.execute(sqlinsert)
                    self.connect.commit()
        except Exception:
            # Rollback in case there is any error
            self.connect.rollback()
            print("发生异常",Exception)
            traceback.print_exc()

        return item

    def close_spider(self, spider):
        self.connect.close()